package sealte.hvpms.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import sealte.hvpms.dao.UserDAO;
import sealte.hvpms.pojo.UserCreate;
import sealte.hvpms.util.DBConnection;

public class UserDAOImpl implements UserDAO {
	private Connection conn = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	
	public String login(String name,String password){
		String message = "用户名或密码不正确";
		String sql = "select uc_id from hvpms_users_create where uc_name=? and uc_password=md5(?)";
		try {
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			ps.setString(2, password);
			rs = ps.executeQuery();
			if(rs.next()){
				int res = rs.getInt(1);
				if(res>-1){
					message = "OK";
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DBConnection.close(rs, ps, conn);
		}
		
		return message;
	}
	
	public boolean register(UserCreate uc){
		boolean flag = false;
		String sql = "insert into hvpms_users_create(uc_name,uc_password,uc_email,uc_gp_ques,uc_gp_answ) values(?,md5(?),?,?,?)";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, uc.getName());
			ps.setString(2, uc.getPassword());
			ps.setString(3, uc.getEmail());
			ps.setInt(4, uc.getQues());
			ps.setString(5, uc.getAnsw());
			ps.executeUpdate();
			flag = true;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(null, ps, conn);
		}
		return flag;
	}
	
	public int getID(String name,String password){
		int id = -1;
		String sql = "select uc_id from hvpms_users_create where uc_name=? and uc_password=md5(?)";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			ps.setString(2, password);
			rs = ps.executeQuery();
			if(rs.next()){
				id = rs.getInt("uc_id");
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(rs, ps, conn);
		}
		return id;
	}
	
	public int getAllRecorders(String kw){
		int ar = 0;
		String sql = null;
		if(kw==null||kw.equals("")){
			sql = "SELECT COUNT(uc_id) from hvpms_users_create";
		}else{
			sql = "SELECT COUNT(uc_id) from hvpms_users_create WHERE uc_name LIKE ?";
		}
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			if (!(kw == null || kw.equals(""))){
				ps.setString(1, "%" + kw + "%");
			}
			rs = ps.executeQuery();
			if(rs.next()){
				ar = rs.getInt(1);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(rs, ps, conn);
		}
		return ar;
	}
	
	public List<UserCreate> listAll(int currentPage,int lineSize,String kw){
		List<UserCreate> all = new ArrayList<UserCreate>();	
		int prevDates = (currentPage -1) * lineSize;
		String sql = null;
		if (kw == null || kw.equals("")) {
			sql = "SELECT * FROM hvpms_users_create limit ?,?";
		} else {
			sql = "SELECT * FROM hvpms_users_create WHERE uc_name LIKE ? limit ?,?";
		}
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			if (!(kw == null || kw.equals(""))) {
				ps.setString(1, "%" + kw + "%");
				ps.setInt(2, prevDates);
				ps.setInt(3, lineSize);
			}else{
				ps.setInt(1, prevDates);
				ps.setInt(2, lineSize);
			}
			rs = ps.executeQuery();
			while(rs.next()){
				UserCreate uc = new UserCreate();
				uc.setId(rs.getInt("uc_id"));
				uc.setEmail(rs.getString("uc_email"));
				uc.setName(rs.getString("uc_name"));
				uc.setRegisterdate(rs.getDate("uc_registerdate"));
				all.add(uc);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(rs, ps, conn);
		}
		return all;
	}
	
	public boolean deleteByID(int id){
		boolean flag = false;
		String sql = "delete from hvpms_users_create where uc_id=?";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.executeUpdate();
			flag = true;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(null, ps, conn);
		}
		return flag;
	}
	
	public boolean batchDeleteByID(int[] uc_ids){
		boolean flag = false;
		String sql = "delete from hvpms_users_create where uc_id=?";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			for(int i=0;i<uc_ids.length;i++){
				ps.setInt(1, uc_ids[i]);
				ps.executeUpdate();
			}
			flag = true;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(null, ps, conn);
		}
		return flag;
	}
	
	public boolean verifyName(String name){
		boolean flag = false;
		String sql = "select * from hvpms_users_create where uc_name=?";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			rs = ps.executeQuery();
			if(rs.next()){
				flag = true;
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(rs, ps, conn);
		}
		return flag;
	}
	
	public boolean isGPQuesRight(String name,int gpQues){
		boolean flag = false;
		String sql = "select * from hvpms_users_create where uc_name=? and uc_gp_ques=?";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			ps.setInt(2, gpQues);
			rs = ps.executeQuery();
			if(rs.next()){
				flag = true;
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(rs, ps, conn);
		}
		return flag;
	}
	
	public boolean isGPAnswRight(String name,int gpQues,String gpAnsw){
		boolean flag = false;
		String sql = "select * from hvpms_users_create where uc_name=? and uc_gp_ques=? and uc_gp_answ=?";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			ps.setInt(2, gpQues);
			ps.setString(3, gpAnsw);
			rs = ps.executeQuery();
			if(rs.next()){
				flag = true;
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(rs, ps, conn);
		}
		return flag;
	}
	
	public boolean resetPassword(String name){
		boolean flag = false;
		String sql = "update hvpms_users_create set uc_password=md5(?) where uc_name=?";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, "666666");
			ps.setString(2, name);
			ps.executeUpdate();
			flag = true;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(null, ps, conn);
		}
		return flag;
	}
	
	public boolean unregister(int id){
		boolean flag = false;
		String sql = "delete from hvpms_users_create where uc_id=?";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.executeUpdate();
			flag = true;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(null, ps, conn);
		}
		return flag;
	}
	
	public boolean validatePassword(int id,String op){
		boolean flag = false;
		String sql = "select * from hvpms_users_create where uc_id=? and uc_password=md5(?)";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.setString(2, op);
			rs = ps.executeQuery();
			if(rs.next()){
				flag = true;
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(rs, ps, conn);
		}
		return flag;
	}
	
	public boolean modifyPassword(int id,String np){
		boolean flag = false;
		String sql = "update hvpms_users_create set uc_password=md5(?) where uc_id=?";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, np);
			ps.setInt(2, id);
			ps.executeUpdate();
			flag = true;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(null, ps, conn);
		}
		return flag;
	}
	
	public String getEmailByID(int id){
		String email = "";
		String sql = "select uc_email from hvpms_users_create where uc_id=?";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if(rs.next()){
				email = rs.getString("uc_email");
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(rs, ps, conn);
		}
		return email;
	}
	
	public boolean resetEmail(int id,String email){
		boolean flag = false;
		String sql = "update hvpms_users_create set uc_email=? where uc_id=?";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, email);
			ps.setInt(2, id);
			ps.executeUpdate();
			flag = true;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(null, ps, conn);
		}
		return flag;
	}
}
